using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaLib;
using System.Data;
using System.ServiceModel;
using System.Data.SqlTypes;
//TODO: Lav evt. "#if DEBUG" og send den egentlige fejl tilbage til clienten for debug purpose only!
namespace DBConnecter
{
    /// <summary>
    /// Data Access layer that communicates with the database.
    /// </summary>
    public static class DatabaseController
    {
        /// <summary>
        /// Implements the reservation to the database.
        /// </summary>
        /// <param name="show">The show that is chosen.</param>
        /// <returns>Returns the confirmation of the reservation.</returns>
        public static Reservation BeginReservation(Show show)
        {
            return new Reservation(Convert.ToInt32(WriteSQL("INSERT INTO dbo.Reservation (dbo.Reservation.IsPaid, dbo.Reservation.ShowId) VALUES (@ISPAID, @SHOWID); SELECT SCOPE_IDENTITY();",
                new string[][] { new string[] { "@ISPAID", "false" }, new string[] { "@SHOWID", show.ID.ToString() } })), false, show);
        }

        /// <summary>
        /// Updates the reservation.
        /// </summary>
        /// <param name="reservation">The reservation containing information about the relations.</param>
        public static void UpdateReservation(Reservation reservation)
        {
            //Re-Insert or change current reservation.
            if (!Convert.ToBoolean(WriteSQL("SELECT CAST(COUNT(1) AS BIT) AS Expr1 FROM dbo.Reservation WHERE dbo.Reservation.Id = @ID", new string[][] { new string[] { "@ID", reservation.ID.ToString() } })))
            {
                if (reservation.ID == 0)
                {
                    WriteSQL("INSERT INTO dbo.Reservation (dbo.Reservation.IsPaid, dbo.Reservation.ShowId) VALUES (@ISPAID, @SHOWID)", new string[][] {
                        new string[] { "@ISPAID", reservation.IsPaid.ToString() }, new string[] { "@SHOWID", reservation.BookedShow.ID.ToString() } });
                }
                else
                {
                    WriteSQL("SET IDENTITY_INSERT dbo.Reservation ON; INSERT INTO dbo.Reservation (dbo.Reservation.Id, dbo.Reservation.IsPaid, dbo.Reservation.ShowId) VALUES (@ID, @ISPAID, @SHOWID); SET IDENTITY_INSERT dbo.Reservation OFF;",
                        new string[][] { new string[] { "@ID", reservation.ID.ToString() }, new string[] { "@ISPAID", reservation.IsPaid.ToString() }, new string[] { "@SHOWID", reservation.BookedShow.ID.ToString() } });
                }
            }
            //Insert user and Apply him/her to the reservation.
            if (reservation.BookedUser != null && reservation.BookedUser.PassCode == null)
            {
                if (reservation.BookedUser.Address != null)
                {
                    reservation.BookedUser.ID = Convert.ToInt32(WriteSQL("INSERT INTO [User] (dbo.[User].Email, dbo.[User].LastName, dbo.[User].FirstName, dbo.[User].Address, dbo.[User].City, dbo.[User].ZipCode, dbo.[User].PhoneNumber, dbo.[User].PassCode) VALUES (" +
                        "(@EMAIL, @LASTNAME, @FIRSTNAME, @ADDRESS, @CITY, @ZIPCODE, @PHONENUMBER, @PASSCODE); SELECT SCOPE_IDENTITY();", new string[][] { new string[] { "@EMAIL", reservation.BookedUser.EMail.ToString() },
                        new string[] { "@LASTNAME", reservation.BookedUser.LastName.ToString() }, new string[] { "@FIRSTNAME", reservation.BookedUser.FirstName.ToString() }, 
                        new string[] { "@ADDRESS", reservation.BookedUser.Address.ToString() }, new string[] { "@CITY", reservation.BookedUser.City.ToString() }, 
                        new string[] { "@ZIPCODE", reservation.BookedUser.ZipCode.ToString() }, new string[] { "@PHONENUMBER", reservation.BookedUser.PhoneNumber.ToString() }, 
                        new string[] { "@PASSCODE", reservation.BookedUser.PassCode.ToString() } }));
                }
                else if (reservation.BookedUser.EMail != null)
                {
                    reservation.BookedUser.ID = Convert.ToInt32(WriteSQL("INSERT INTO [User] (dbo.[User].Email, dbo.[User].LastName, dbo.[User].FirstName, dbo.[User].PhoneNumber, dbo.[User].PassCode) VALUES (" +
                        "@EMAIL, @LASTNAME, @FIRSTNAME, @PHONENUMBER, @PASSCODE); SELECT SCOPE_IDENTITY();", new string[][] { new string[] { "@EMAIL", reservation.BookedUser.EMail.ToString() },
                        new string[] { "@LASTNAME", reservation.BookedUser.LastName.ToString() }, new string[] { "@FIRSTNAME", reservation.BookedUser.FirstName.ToString() }, 
                        new string[] { "@PHONENUMBER", reservation.BookedUser.PhoneNumber.ToString() }, new string[] { "@PASSCODE", reservation.BookedUser.PassCode.ToString() } }));
                }
                else
                {
                    reservation.BookedUser.ID = Convert.ToInt32(WriteSQL("INSERT INTO dbo.[User] (dbo.[User].LastName, dbo.[User].FirstName, dbo.[User].PhoneNumber) VALUES (" +
                        "@LASTNAME, @FIRSTNAME, @PHONENUMBER); SELECT SCOPE_IDENTITY();", new string[][] { new string[] { "@LASTNAME", reservation.BookedUser.LastName.ToString() }, 
                        new string[] { "@FIRSTNAME", reservation.BookedUser.FirstName.ToString() }, new string[] { "@PHONENUMBER", reservation.BookedUser.PhoneNumber.ToString() } }));
                }
                WriteSQL("UPDATE dbo.Reservation SET dbo.Reservation.UserId = @USERID WHERE dbo.Reservation.Id = @ID",
                        new string[][] { new string[] { "@USERID", reservation.BookedUser.ID.ToString() }, new string[] { "@ID", reservation.ID.ToString() } });
            }
            else if (reservation.BookedUser != null && reservation.BookedUser.PassCode != null)
            {
                WriteSQL("UPDATE dbo.Reservation SET dbo.Reservation.UserId = @USERID WHERE dbo.Reservation.Id = @ID",
                    new string[][] { new string[] { "@USERID", reservation.BookedUser.ID.ToString() }, new string[] { "@ID", reservation.ID.ToString() } });
            }
            //Removes seat which is no longer selected.
            SqlDataReader data = ReadSQL("SELECT dbo.Ticket.Id, dbo.Ticket.SeatId FROM dbo.Ticket WHERE dbo.Ticket.ReservationId = @RESERVATIONID AND dbo.Ticket.ShowId = @SHOWID",
                new string[][] { new string[] { "@RESERVATIONID", reservation.ID.ToString() }, new string[] { "@SHOWID", reservation.BookedShow.ID.ToString() } });
            List<int> ids = new List<int>();
            while (data.Read())
            {
                if (reservation.Tickets.Any(t => t.Seat.ID == data.GetInt32(1)))
                {
                    ids.Add(data.GetInt32(1));
                }
                else
                {
                    WriteSQL("DELETE FROM dbo.Ticket WHERE dbo.Ticket.Id = @ID", new string[][] { new string[] { "@ID", data.GetInt32(0).ToString() } });
                }
            }
            //Evaluate seats and lock them, if they're already locked fault exception will be handled at the client...
            string[][] pararr = new string[reservation.Tickets.Count * 3][];
            string[] sqlcmd = new string[reservation.Tickets.Count];
            for (int i = 0; i < reservation.Tickets.Count; i++)
            {
                if (ids.Any(t => t == reservation.Tickets[i].Seat.ID))
                {
                    continue;
                }
                else
                {
                    sqlcmd[i] = "INSERT INTO dbo.Ticket (dbo.Ticket.ReservationId, dbo.Ticket.SeatId, dbo.Ticket.ShowId, dbo.Ticket.TicketType) VALUES ( " + reservation.ID.ToString() + ", @SEATID" + i + ", @SHOWID" + i + ", @TICKETTYPE" + i + ")";
                    pararr[3 * i] = new string[] { "@SEATID" + i, reservation.Tickets[i].Seat.ID.ToString() };
                    pararr[3 * i + 1] = new string[] { "@SHOWID" + i, reservation.BookedShow.ID.ToString() };
                    pararr[3 * i + 2] = new string[] { "@TICKETTYPE" + i, reservation.Tickets[i].TicketType.ToString() };
                }
            }
            TransactSQL(sqlcmd, pararr);
        }

        /// <summary>
        /// Places an order assosiated with a user, reservation, show and writes it to the database.
        /// </summary>
        /// <param name="reservation">The reservation containing information about the relations.</param>
        public static void Order(Reservation reservation)
        {
            string[][] pararr = new string[(reservation.Tickets.Count * 3) + 3][];
            string[] sqlcmd = new string[reservation.Tickets.Count + 1];
            sqlcmd[0] = "INSERT INTO dbo.Reservation (dbo.Reservation.IsPaid, dbo.Reservation.ShowId, dbo.Reservation.UserId) VALUES (@ISPAID, @RESERVATIONID, @USERID); DECLARE @LASTID int; SELECT @LASTID = SCOPE_IDENTITY(); ";
            pararr[0] = new string[] { "@ISPAID", Convert.ToInt32(reservation.IsPaid).ToString() };
            pararr[1] = new string[] { "@RESERVATIONID", reservation.ID.ToString() };
            pararr[2] = new string[] { "@USERID", reservation.BookedUser.ID.ToString() };

            for (int i = 0; i < reservation.Tickets.Count; i++)
            {
                //sqlcmd += "INSERT INTO dbo.Ticket (dbo.Ticket.ReservationId, dbo.Ticket.SeatId, dbo.Ticket.ShowId, dbo.Ticket.TicketType) VALUES (@LASTID, @SEATID" + i + ", @SHOWID" + i + ", @TICKETTYPE" + i + "); ";
                pararr[3 * (i + 1)] = new string[] { "@SEATID" + i, reservation.Tickets[i].Seat.ID.ToString() };
                pararr[3 * (i + 1) + 1] = new string[] { "@SHOWID" + i, reservation.BookedShow.ID.ToString() };
                pararr[3 * (i + 1) + 2] = new string[] { "@TICKETTYPE" + i, reservation.Tickets[i].TicketType.ToString() };
            }
        }

        /// <summary>
        /// Cancels a reservation by it's ID.
        /// </summary>
        /// <param name="reservationid">A string containing the ID of the user's reservation.</param>
        public static void Cancel(string reservationid)
        {
            SqlDataReader data = ReadSQL("SELECT dbo.Reservation.Id, dbo.Reservation.IsPaid, dbo.Reservation.ShowId, dbo.Reservation.UserId FROM dbo.Reservation WHERE dbo.Reservation.Id = @ID",
                    new string[][] { new string[] { "@ID", reservationid } });
            if (data.Read())
            {
                WriteSQL("DELETE FROM dbo.Ticket WHERE dbo.Ticket.ReservationId = @RESEVATIONID",
                    new string[][] { new string[] { "@RESEVATIONID", reservationid } });
                WriteSQL("DELETE FROM dbo.Reservation WHERE dbo.Reservation.Id = @ID", new string[][] { new string[] { "@ID", reservationid } });
                if (!data.IsDBNull(3))
                {
                    if (!Convert.ToBoolean(WriteSQL("SELECT CAST(COUNT(1) AS BIT) AS Expr1 FROM dbo.[User] WHERE dbo.[User].Id = @USERID AND dbo.[User].Passcode IS NULL",
                        new string[][] { new string[] { "@USERID", data.GetInt32(3).ToString() } })))
                    {
                        WriteSQL("DELETE FROM dbo.[User] WHERE dbo.[User].Id = @USERID",
                            new string[][] { new string[] { "@USERID", data.GetInt32(3).ToString() } });
                    }
                }
            }
        }

        /// <summary>
        /// Get a certain hall requested by ID.
        /// </summary>
        /// <param name="hallid">The number or name of a hall.</param>
        /// <returns>A hall with rows and seats.</returns>
        public static Hall GetHall(int hallid, int showid)
        {
            Hall rethall = new Hall(hallid);
            SqlDataReader halldata;
            if ((halldata = ReadSQL("SELECT dbo.Row.Number, dbo.Seat.Id, dbo.Seat.Number, dbo.Seat.Type, dbo.Ticket.SeatId FROM " +
                "dbo.Hall INNER JOIN dbo.Row ON dbo.Row.HallId = dbo.Hall.Id INNER JOIN dbo.Seat ON dbo.Seat.RowId = dbo.Row.Id " +
                "LEFT JOIN dbo.Ticket ON dbo.Ticket.SeatId = dbo.Seat.Id AND dbo.Ticket.ShowId = @SHOWID WHERE dbo.Hall.Id = @ID ORDER BY RowId ASC",
                new string[][] { new string[] { "@ID", hallid.ToString() }, new string[] { "@SHOWID", showid.ToString() } })) == null)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + "Internal SQL null error");
            }

            while (halldata.Read())
            {
                if (!rethall.Rows.Any(r => r.Number == halldata.GetInt32(0)))
                {
                    rethall.Rows.Add(new Row(halldata.GetInt32(0)));
                }
                if (!halldata.IsDBNull(4) && halldata.GetInt32(4) == halldata.GetInt32(1))
                {
                    rethall.Rows.Find(r => r.Number == halldata.GetInt32(0)).Seats.Add(new Seat(halldata.GetInt32(1), halldata.GetInt32(2), (SeatType)Enum.Parse(typeof(SeatType), halldata.GetString(3)), true));
                }
                else
                {
                    rethall.Rows.Find(r => r.Number == halldata.GetInt32(0)).Seats.Add(new Seat(halldata.GetInt32(1), halldata.GetInt32(2), (SeatType)Enum.Parse(typeof(SeatType), halldata.GetString(3)), false));
                }
            }
            rethall.Rows.Reverse(0, rethall.Rows.Count);
            return rethall;
        }

        /// <summary>
        /// Requests a list of shows.
        /// </summary>
        /// <param name="searchword">Search by name.</param>
        /// <param name="searchstarttime">Search by start time of show.</param>
        /// <param name="searchendtime">Search by end time of show.</param>
        /// <returns>Returns a list of shows.</returns>
        public static List<Show> GetShow(string searchword, DateTime searchstarttime, DateTime searchendtime)
        {
            List<Show> retshows = new List<Show>();
            SqlDataReader showdata;
            if ((showdata = ReadSQL("SELECT dbo.Show.Name, dbo.Show.StartTime, dbo.Show.Length, dbo.Show.HallId, dbo.Show.MovieId, " + //0 to 4
                "dbo.Movie.Name, dbo.Movie.Description, dbo.Movie.AgeLimit, dbo.Movie.[Language], dbo.Movie.Subtitles, dbo.Movie.Genre, " + //5 to 10
                "dbo.Movie.RunTime, dbo.Movie.ThirdDimensional, dbo.Show.Id, dbo.Show.Price, dbo.Movie.Id " + //11 to 12
                "FROM dbo.Show INNER JOIN dbo.Movie ON dbo.Show.MovieId = dbo.Movie.Id WHERE dbo.Show.Name LIKE @SEARCHWORD AND dbo.Show.StartTime BETWEEN @STARTTIME AND @ENDTIME",
                new string[][] { new string[] { "@SEARCHWORD", "%" + searchword + "%" }, new string[] { "@STARTTIME", searchstarttime.ToString("yyyy-MM-dd") }, 
                new string[] { "@ENDTIME", (searchendtime + new TimeSpan(1,0,0,0)).ToString("yyyy-MM-dd") }, })) == null)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + "Internal SQL null error");
            }

            while (showdata.Read())
            {
                retshows.Add(new Show(showdata.GetInt32(13), showdata.GetString(0), showdata.GetDouble(14), showdata.GetDateTime(1), showdata.GetTimeSpan(2), new Hall(showdata.GetInt32(3)),
                    new Movie(showdata.GetInt32(15), showdata.GetString(5).ToString(), showdata.GetString(6), showdata.GetByte(7), (Language)Enum.Parse(typeof(Language), showdata.GetString(8)),
                    (Language)Enum.Parse(typeof(Language), showdata.GetString(9)),(Genre)Enum.Parse(typeof(Genre), showdata.GetString(10)), showdata.GetTimeSpan(11), showdata.GetBoolean(12))));
            }

            return retshows;
        }

        /// <summary>
        /// Requests a list of shows within a timespan.
        /// </summary>
        /// <param name="hallid">The ID of the hall.</param>
        /// <param name="searchstarttime">Searches the start time of the requested timespan.</param>
        /// <param name="searchendtime">Searches the end time of the requested timespan.</param>
        /// <returns>Returns a list of times of shows within the requested timespan.</returns>
        public static List<Show> GetSchedule(string hallid, DateTime searchstarttime, DateTime searchendtime)
        {
            List<Show> retshows = new List<Show>();
            SqlDataReader showdata;
            if ((showdata = ReadSQL("SELECT dbo.Show.Name, dbo.Show.StartTime, dbo.Show.Length, dbo.Show.HallId, dbo.Show.MovieId, " + //0 to 4
                "dbo.Movie.Name, dbo.Movie.Description, dbo.Movie.AgeLimit, dbo.Movie.[Language], dbo.Movie.Subtitles, dbo.Movie.Genre, " + //5 to 10
                "dbo.Movie.RunTime, dbo.Movie.ThirdDimensional, dbo.Show.Id, dbo.Show.Price, dbo.Movie.Id " + //11 to 14
                "FROM dbo.Show INNER JOIN dbo.Movie ON dbo.Show.MovieId = dbo.Movie.Id WHERE dbo.Show.HallId = @HALLID AND dbo.Show.StartTime BETWEEN @STARTTIME AND @ENDTIME ORDER BY " +
                "dbo.Show.StartTime ASC", new string[][] { new string[] { "@HALLID", hallid }, new string[] { "@STARTTIME", searchstarttime.ToString() }, new string[] { "@ENDTIME", (searchendtime + new TimeSpan(1, 0, 0, 0)).ToString("yyyy-MM-dd") } })) == null)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + "Internal SQL null error");
            }

            while (showdata.Read())
            {
                retshows.Add(new Show(showdata.GetInt32(13), showdata.GetString(0), showdata.GetDouble(14), showdata.GetDateTime(1), showdata.GetTimeSpan(2), new Hall(showdata.GetInt32(3)), 
                    new Movie(showdata.GetInt32(15), showdata.GetString(5), showdata.GetString(6), showdata.GetByte(7), (Language)Enum.Parse(typeof(Language), showdata.GetString(8)),
                    (Language)Enum.Parse(typeof(Language), showdata.GetString(9)), (Genre)Enum.Parse(typeof(Genre), showdata.GetString(10)), showdata.GetTimeSpan(11), showdata.GetBoolean(12))));
            }

            return retshows;
        }

        /// <summary>
        /// Contacts the database and requests a list of shows containing a movie within a timespan.
        /// </summary>
        /// <param name="movie">Movie to find the shows from.</param>
        /// <param name="searchstarttime">Start time limit.</param>
        /// <param name="searchendtime">End time limit.</param>
        /// <returns>A list of all shows with a certain movie within a timespan.</returns>
        public static List<Show> GetShowWithMovie(Movie movie, DateTime searchstarttime, DateTime searchendtime)
        {
            List<Show> retshows = new List<Show>();
            SqlDataReader showdata;
            if ((showdata = ReadSQL("SELECT dbo.Show.Id, dbo.Show.Name, dbo.Show.Price, dbo.Show.StartTime, dbo.Show.Length, dbo.Show.HallId " +
                "FROM dbo.Show WHERE dbo.Show.MovieId = @MOVIEID AND dbo.Show.StartTime BETWEEN @STARTTIME AND @ENDTIME ORDER BY " +
                "dbo.Show.StartTime ASC", new string[][] { new string[] { "@MOVIEID", movie.ID.ToString() }, new string[] { "@STARTTIME", searchstarttime.ToString("yyyy-MM-dd") }, new string[] { "@ENDTIME", (searchendtime + new TimeSpan(1, 0, 0, 0)).ToString("yyyy-MM-dd") } })) == null)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + "Internal SQL null error");
            }
            while (showdata.Read())
            {
                retshows.Add(new Show(showdata.GetInt32(0), showdata.GetString(1), showdata.GetDouble(2), showdata.GetDateTime(3), showdata.GetTimeSpan(4), GetHall(showdata.GetInt32(5), showdata.GetInt32(0)), movie));
            }
            return retshows;
        }

        /// <summary>
        /// Verify a user login and returns the user if the login was correct.
        /// </summary>
        /// <param name="username">Username.</param>
        /// <param name="passcode">Password.</param>
        /// <returns>The requested user if verification was valid otherwise null.</returns>
        public static User VerifyUser(string username, string passcode)
        {
            SqlDataReader userdata;
            if ((userdata = ReadSQL("SELECT dbo.[User].Id, dbo.[User].FirstName, dbo.[User].LastName, dbo.[User].Email, dbo.[User].PhoneNumber, dbo.[User].Address, dbo.[User].City, dbo.[User].ZipCode, dbo.[User].PassCode, " + 
                "FROM dbo.[User] WHERE dbo.[User].Email = @USERNAME AND dbo.[User].PassCode = @PASSCODE", new string[][] { new string[] { "@USERNAME", username }, new string[] { "@PASSCODE", passcode } })) == null)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + "Internal SQL null error");
            }
            if (userdata.Read())
            {
                return new User(userdata.GetString(1), userdata.GetString(2), userdata.GetInt32(4), userdata.GetString(8), userdata.GetString(3), userdata.GetString(5), userdata.GetString(6), userdata.GetInt32(7));
            }
            else
            {
                throw new FaultException("User does not exist");
            }
        }

        /// <summary>
        /// Checks if the user exists.
        /// </summary>
        /// <param name="user">User.</param>
        /// <returns>Returns true or false.</returns>
        public static bool UserExist(User user)
        {
            SqlDataReader userdata;
            if ((userdata = ReadSQL("SELECT dbo.[User].Email, dbo.[User].PassCode FROM dbo.[User] WHERE dbo.[User].Email = @USERNAME", new string[][] { new string[] { "@USERNAME", user.EMail } })) == null)
            {
                return false;
            }

            if (userdata.Read())
            {
                if (!userdata.IsDBNull(1) || userdata.GetString(1) != "")
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Creates a new user if it doesn't already exist.
        /// </summary>
        /// <param name="user">A user that should be created.</param>
        public static void CreateUser(User user)
        {
            WriteSQL("INSERT INTO [User] (Email, LastName, FirstName, Address, City, ZipCode, PhoneNumber, PassCode) VALUES ('" +
                "@EMAIL, @LASTNAME, @FIRSTNAME, @ADDRESS, @CITY, @ZIPCODE, @PHONENUMBER, @PASSCODE", new string[][] { new string[] { "@EMAIL", user.EMail },
                new string[] { "@LASTNAME", user.LastName }, new string[] { "@FIRSTENAME", user.FirstName }, new string[] { "@ADDRESS", user.Address }, new string[] { " @CITY", user.City },
                new string[] { " @ZIPCODE", user.ZipCode.ToString() }, new string[] { " @PHONENUMBER", user.PhoneNumber.ToString() }, new string[] { " @PASSCODE", user.PassCode } });
        }

        /// <summary>
        /// Removes the temporary locked reservations after a specified timespan.
        /// </summary>
        /// <param name="reservation">Reservation to be removed.</param>
        public static void RemoveTimeout(Reservation reservation)
        {
            if(reservation.BookedUser != null)
            {
                WriteSQL("DELETE FROM dbo.User WHERE dbo.User.Id = @ID", new string[][] { new string[] { "@ID", reservation.BookedUser.ID.ToString() } });
            }
            string[][] pararr = new string[reservation.Tickets.Count * 2][];
            string[] sqlcmd = new string[reservation.Tickets.Count];
            for (int i = 0; i < reservation.Tickets.Count; i++)
            {
                sqlcmd[i] = "DELETE FROM dbo.Ticket WHERE dbo.Ticket.ReservationId = @RESEVATIONID" + i + " AND dbo.Ticket.ShowId = @SHOWID" + i;
                pararr[2 * i] = new string[] { "@RESEVATIONID" + i, reservation.ID.ToString() };
                pararr[2 * i + 1] = new string[] { "@SHOWID" + i, reservation.BookedShow.ID.ToString() };
            }
            TransactSQL(sqlcmd, pararr);
            WriteSQL("DELETE FROM dbo.Reservation WHERE dbo.Reservation.Id = @ID", new string[][] { new string[] { "@ID", reservation.ID.ToString() } });
        }

        /// <summary>
        /// Requests a list of the newest movies added to the database.
        /// </summary>
        /// <param name="number">Contains an integer with a number which these movies have assigned.</param>
        /// <returns>Returns a list of the newest movies.</returns>
        public static List<Movie> GetNewestMovies(int number)
        {
            SqlDataReader data = ReadSQL("SELECT TOP(@COUNT) dbo.Movie.Id, dbo.Movie.Name, dbo.Movie.Description, dbo.Movie.AgeLimit, dbo.Movie.[Language], dbo.Movie.Subtitles, dbo.Movie.Genre, " +
            "dbo.Movie.RunTime, dbo.Movie.ThirdDimensional FROM dbo.Movie ORDER BY dbo.Movie.Id DESC", new object[][] { new object[] { "@COUNT", number } });
            List<Movie> ret = new List<Movie>();
            while (data.Read())
            {
                ret.Add(new Movie(data.GetInt32(0), data.GetString(1), data.GetString(2), data.GetByte(3), (Language)Enum.Parse(typeof(Language), data.GetString(4)), 
                    (Language)Enum.Parse(typeof(Language), data.GetString(5)), (Genre)Enum.Parse(typeof(Genre), data.GetString(6)), data.GetTimeSpan(7), data.GetBoolean(8)));
            }
            return ret;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        /// <returns>Returns an object from the command.</returns>
        private static SqlDataReader ReadSQL(string cmd, params object[][] val)
        {
            try
            {
                SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString);
                
                sqlcon.Open();
                SqlCommand sqlcmd = new SqlCommand(cmd, sqlcon);
                for (int i = 0; i < val.Length; i++)
                {
                    if (cmd == null || val[i][0] == null)
                    {
                        continue;
                    }
                    sqlcmd.Parameters.AddWithValue(val[i][0].ToString(), val[i][1]);
                }
                sqlcmd.Dispose();
                return sqlcmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }

        /// <summary>
        /// Writes command to the database.
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        /// <returns>Returns an object from the command.</returns>
        private static object WriteSQL(string cmd, params object[][] val)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                {
                    sqlcon.Open();
                    SqlCommand sqlcmd = new SqlCommand(cmd, sqlcon);
                    for (int i = 0; i < val.Length; i++)
                    {
                        if (cmd == null || val[i][0] == null)
                        {
                            continue;
                        }
                        sqlcmd.Parameters.AddWithValue(val[i][0].ToString(), val[i][1]);
                    }
                    return sqlcmd.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }

        /// <summary>
        /// Start a transaction in the database.
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed in the transaction.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        private static void TransactSQL(string[] cmd, params object[][] val)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                {
                    sqlcon.Open();
                    SqlTransaction sqltransaction = sqlcon.BeginTransaction(IsolationLevel.ReadUncommitted, "Order");
                    for (int i = 0; i < cmd.Length; i++)
                    {
                        if (cmd[i] == null)
                        {
                            continue;
                        }
                        SqlCommand sqlcmd = new SqlCommand(cmd[i], sqlcon, sqltransaction);
                        for (int j = 0; j < (int)(val.Length / cmd.Length); j++)
                        {
                            if (val[(val.Length / cmd.Length * i) + j][0] == null)
                            {
                                continue;
                            }
                            sqlcmd.Parameters.AddWithValue(val[(val.Length / cmd.Length * i) + j][0].ToString(), val[(val.Length / cmd.Length * i) + j][1]);
                        }
                        sqlcmd.ExecuteScalar();
                    }
                    try
                    {
                        sqltransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        //If this happens something is wrong...
                        try
                        {
                            sqltransaction.Rollback();
                        }
                        //If any of this happens some one must really have fucked things up...
                        catch (InvalidOperationException exce)
                        {
                            throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + exce.ToString());
                        }
                        catch (Exception exce)
                        {
                            throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + exce.ToString());
                        }
                        throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }
    }
}
